package cn.itlaobing.eshop.web.dao;

import cn.itlaobing.eshop.web.entity.DetailEntity;
import cn.itlaobing.eshop.web.entity.DtoEntity;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * Created by IT-XuGeek on 2017/11/14.
 */
public class DtoDao extends BaseDao{
    public List<DtoEntity> qurrygoods()throws SQLException {
    String sql="SELECT g.id,g.price,g.good_name,p.picUrl FROM itlb_goods g LEFT JOIN " +
            "(SELECT good_id,SUM(buy_count)sales_count " +
            "FROM itlb_order_details GROUP BY good_id)s " +
            " ON g.id=s.good_id" +
            " LEFT JOIN itlb_goods_pic p " +
            " ON g.id=p.goodsId " +
            " WHERE g.status_line=1 " +
            " ORDER BY s.sales_count limit 0,4" ;
        QueryRunner qr=new QueryRunner(getDataSource());
        BeanListHandler<DtoEntity> beanListHandler=new BeanListHandler<DtoEntity>(DtoEntity.class);
        return  qr.query(sql,beanListHandler);

}
    public List<DtoEntity> qurryByGoodsIsNew()throws SQLException{
        String sql="SELECT g.id,g.good_name,g.price,p.picUrl FROM itlb_goods g INNER JOIN itlb_goods_pic p  " +
                " ON g.id=p.goodsId " +
                " INNER JOIN base_goods b " +
                " ON g.id=b.goodsId " +
                " WHERE goodsIsNew=0 LIMIT 0,6";
        QueryRunner qr=new QueryRunner(getDataSource());
        BeanListHandler<DtoEntity> beanListHandler=new BeanListHandler<DtoEntity>(DtoEntity.class);
        return  qr.query(sql,beanListHandler);
    }
    public List<DetailEntity> goodsDetail()throws SQLException{
        String sql="SELECT g.id,g.good_name,g.price,p.picUrl,g.detail FROM itlb_goods g INNER JOIN itlb_goods_pic p ON g.id=p.goodsId WHERE g.id=2";
        QueryRunner qr=new QueryRunner(getDataSource());
        BeanListHandler<DetailEntity> beanListHandler=new BeanListHandler<DetailEntity>(DetailEntity.class);
        return  qr.query(sql,beanListHandler);
    }

}